<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %><!-- 数据库操作需要 -->        
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
		//获取参数，转换为相应的类型
		String RequestID=request.getParameter("RequestID");
		String UsersNum=request.getParameter("UsersNum");
		int UsersNumInt=Integer.parseInt(UsersNum);
		String UseRange=request.getParameter("UseRange");
		String Userphone=request.getParameter("Userphone");
		String UseTime=request.getParameter("UseTime");
		String ReturnTime=request.getParameter("ReturnTime");
		String degree=request.getParameter("degree");
		String EmpID=request.getParameter("EmpID");
		String applyTime=request.getParameter("applyTime");
		String applyDepartment=request.getParameter("applyDepartment");
		String origin=request.getParameter("origin");
		String goPlace=request.getParameter("goPlace");
		String UseReasons=request.getParameter("UseReasons");
		String Remarks=request.getParameter("Remarks");
		String Attachment =request.getParameter("Attachment");
		String Status="待审核";
		String Did="待指派";
		String license_plate="待指派";
		String Dname="尚未确定";
		String Dphone="尚未确定";
		String ID=null;
		//第一个java代码段查询数据
		try{
			Class.forName("org.h2.Driver");//加载驱动
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return;
		}
		String url="jdbc:h2:d:/CompanyCar/temp/mydbtest";
		Connection conn=null;

	//数据库连接与查询过程中可能会引发sql异常
	try {
		//获取数据库连接(url,username,password)			
		conn = DriverManager.getConnection(url,"sa","");
		//准备执行数据库插入，此处使用了?方式，比拼接字符串更清晰
		String sql="select RequestID from CarRequest ORDER BY RequestID";
		String sql2="insert into CarRequest(RequestID,UsersNum,UseRange,Userphone,UseTime,ReturnTime,degree,EmpID,applyTime,applyDepartment,origin,goPlace,UseReasons,Remarks,Attachment,Status,Did,license_plate) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String sql5="insert into EmployeeCarInfo(RequestID,EmpID,UseTime,ReturnTime,Did,Dname,Dphone,license_plate,UsersNum,origin,goPlace) values(?,?,?,?,?,?,?,?,?,?,?)";
		Statement stmt=conn.createStatement();
		//执行数据库查询，获取结果集
		ResultSet rs=stmt.executeQuery(sql);
		//循环获取结果集当中的所有数据
		while (rs.next()){
			ID=rs.getString("RequestID");	//获取第1个字段
		}
		System.out.println("ID:"+ID);
		stmt.close();	
		if(ID!=null)
			RequestID=String.valueOf(Integer.parseInt(ID)+1);
		else
			RequestID="1";
		System.out.println(RequestID);
		PreparedStatement pstmt=conn.prepareStatement(sql2);
		//准备SQL执行;注意此处与Statement的不同
		pstmt.setString(1,RequestID);
		pstmt.setInt(2,UsersNumInt);
		pstmt.setString(3,UseRange);
		pstmt.setString(4,Userphone);
		pstmt.setString(5,UseTime);
		pstmt.setString(6,ReturnTime);
		pstmt.setString(7,degree);
		pstmt.setString(8,EmpID);
		pstmt.setString(9,applyTime);
		pstmt.setString(10,applyDepartment);
		pstmt.setString(11,origin);
		pstmt.setString(12,goPlace);
		pstmt.setString(13,UseReasons);
		pstmt.setString(14,Remarks);
		pstmt.setString(15,Attachment);
		pstmt.setString(16,Status);
		pstmt.setString(17,Did);
		pstmt.setString(18,license_plate);
		//执行数据库插入，获取结果集
		int result=pstmt.executeUpdate();
		
		pstmt=conn.prepareStatement(sql5);
		pstmt.setString(1,RequestID);
		pstmt.setString(2,EmpID);
		pstmt.setString(3,UseTime);
		pstmt.setString(4,ReturnTime);
		pstmt.setString(5,Did);
		pstmt.setString(6,Dname);
		pstmt.setString(7,Dphone);
		pstmt.setString(8,license_plate);
		pstmt.setInt(9,UsersNumInt);
		pstmt.setString(10,origin);
		pstmt.setString(11,goPlace);
		result=pstmt.executeUpdate();
		//关闭表达式对象
		pstmt.close();			
		//关闭数据库			
		conn.close();			
		
	} catch (SQLException e) {
		e.printStackTrace();
	} finally{
		//防止数据库未正常关闭
		try{
			if (conn!=null && (!conn.isClosed())){
				conn.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}

	request.setAttribute("addok","1");

	request.getRequestDispatcher("/X-UseCar_CheckList.jsp").forward(request,response);
%>
</body>
</html>